﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace NG
{
    class InterviewHnadler:IInterviewHandler
    {
        private string tableName = "[dbo].[Interviews]";
        public void addInterview(Interview inter)
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string sqlDateFormat1 = inter.openDate.ToString("yyy-MM-dd HH:mm");
            string sqlDateFormat2 = inter.closeDate.ToString("yyy-MM-dd HH:mm");
            string query = "INSERT INTO" + tbn + "(project,interviewee,interviewDesc,openDate,closeDate) VALUES("+inter.project +","+ inter.interviewee + ",'" + inter.interviewDesc
                + "','" + sqlDateFormat1 + "','" + sqlDateFormat2 + "');"
            + "SELECT interviewNo FROM" + tbn + "WHERE interviewNo = @@IDENTITY;";
            SqlCommand cmd = new SqlCommand(query, con.connection);
            inter.interviewNo = int.Parse(cmd.ExecuteScalar().ToString());
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't discon`nect from the Database");
            }
        }

        public void deleteInterview(Interview inter)
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;

            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }

            string query = "DELETE FROM " + tbn + " WHERE interviewNo = '" + inter.interviewNo + "'";
            SqlCommand cmd = new SqlCommand(query, con.connection);
            cmd.ExecuteNonQuery();

            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
        }

        public void updateInterview(Interview ointer, Interview ninter)
        {
            DBConnect con = new DBConnect();
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }

            string tbn = "[" + con.dbName + "]." + this.tableName;
            string sqlDateFormat1 = ninter.openDate.ToString("yyy-MM-dd HH:mm");
            string sqlDateFormat2 = ninter.closeDate.ToString("yyy-MM-dd HH:mm");
            string query = "UPDATE  " + tbn + " SET project= " + ninter.project +",interviewee = " + ninter.interviewee + ",interviewDesc ='" +
                ninter.interviewDesc + "',openDate= '" + sqlDateFormat1 + "',closeDate = '" +sqlDateFormat2
            +"' WHERE interviewNo = '" + ointer.interviewNo + "';";
            SqlCommand cmd = new SqlCommand(query, con.connection);
            cmd.ExecuteNonQuery();
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
        }

        public LinkedList<Interview> allInterviews(string sortedBy,bool desc)
        {
            string sorted;
            if (sortedBy == string.Empty)
            {
                sorted = "{0}";
            }
            else
            {
                sorted = sortedBy;
            }
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            LinkedList<Interview> interviewsList = new LinkedList<Interview>();
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string extra = "";
            if (desc)
            {
                extra = " DESC";
            }
            
            string query = "SELECT * FROM " + tbn + "ORDER BY " + sorted + extra;
            SqlCommand cmd = new SqlCommand(query, con.connection);
            SqlDataReader rdr = cmd.ExecuteReader();


            if (rdr.HasRows)
            {

                while (rdr.Read())
                {
                    DateTime dt1 = rdr.GetDateTime(rdr.GetOrdinal("openDate"));
                    DateTime dt2 = rdr.GetDateTime(rdr.GetOrdinal("closeDate"));
                    Interview inter = new Interview(int.Parse(rdr["interviewNo"].ToString()), int.Parse(rdr["project"].ToString()), int.Parse(rdr["interviewee"].ToString()), rdr["interviewDesc"].ToString(), dt1, dt2);
                        

                    interviewsList.AddLast(inter);


                }
            }
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
            return interviewsList;
        }
    
    }
}
